const { success, badRequest, serverError } = require('../utils/response')
const publicModel = require('../models/public.model')
const sqlQueryData = require('./sqlQueryData/deviceSqlQuery')
const multer = require('multer')
const xlsx = require('xlsx')

// 文件上传中间件
const upload = multer({ dest: 'uploads/' })

// 创建文章接口（前端调用 POST /api/Finance，需鉴权）
exports.createFinance = async (req, res) => {
  console.log('插入用户:', req.body)
  try {
    const { title, content } = req.body
    if (!title || !content) {
      return res.status(400).json(badRequest('标题和内容不能为空'))
    }

    const finance = await publicModel.create(
      sqlQueryData.createWechatBillingInsertQuery({ ...req.body })
    )

    res.status(200).json(success(null, '用户添加成功'))
  } catch (err) {
    if (res.headersSent) return
    res.status(500).json(serverError(err.message))
  }
}

// 查询文章列表接口（前端调用 GET /api/articles）
exports.getFinance = async (req, res) => {
  try {
    const { status, tags } = req.body
    console.log('查询文章列表:', status, tags, req.body)
    const result = await publicModel.getList(
      sqlQueryData.searchEquipmentNumberQuery({ status, tags })
    )
    res.json(success(result, '查询成功'))
  } catch (err) {
    if (res.headersSent) return
    res.status(500).json(serverError(err.message))
  }
}

// 导入表格接口
exports.importFinance = [
  upload.single('file'), // 前端 formData 的字段名为 file
  async (req, res) => {
    try {
      if (!req.file) {
        return res.status(400).json(badRequest('请上传文件'))
      }
      // 读取 Excel/CSV 文件
      const workbook = xlsx.readFile(req.file.path)
      const sheetName = workbook.SheetNames[0]
      const sheet = workbook.Sheets[sheetName]
      const data = xlsx.utils.sheet_to_json(sheet)

      const headerMap = {
        '交易时间': 'trade_time',
        '公众号id': 'official_id',
        '商户号': 'busi_no',
        '特约商户号': 'sub_busi_no',
        '设备号': 'equ_num',
        '微信订单号': 'wx_trade_num',
        '商户订单号': 'out_order_code',
        '用户标识': 'user_openid',
        '交易类型': 'trade_type',
        '交易状态': 'trade_stat',
        '付款银行': 'pay_bank',
        '货币种类': 'coin_type',
        '应结订单金额': 'order_should_fee',
        '代金券金额': 'coupon_fee',
        '微信退款单号': 'wx_refund_num',
        '商户退款单号': 'busi_refund_num',
        '退款金额': 'refund_fee',
        '充值券退款金额': 'charge_refund_fee',
        '退款类型': 'refund_type',
        '退款状态': 'refund_stat',
        '商品名称': 'goods_name',
        '商户数据': 'busi_data',
        '手续费': 'service_fee',
        '费率': 'fee_ratio',
        '订单金额': 'order_fee',
        '申请退款金额': 'apply_refund_fee',
        '费率备注': 'fee_ratio_memo',
      }
      // 批量插入
      for (const row of data) {
        // 将表头字段映射为数据库字段
        const mappedRow = {}
        for (const key in row) {
          if (headerMap[key]) {
            mappedRow[headerMap[key]] = row[key]
          }
        }
        // 去别的表查 platform_name_id 和 org_id
        const otherInfo = await publicModel.getOne(
          `SELECT platform_name_id, org_id FROM other_table WHERE wx_trade_num='${mappedRow.wx_trade_num}' LIMIT 1`
        );
        if (otherInfo) {
          mappedRow.platform_name_id = isFinite(Number(otherInfo.platform_name_id)) ? Number(otherInfo.platform_name_id) : 0;
          mappedRow.org_id = isFinite(Number(otherInfo.org_id)) ? Number(otherInfo.org_id) : 0;
        } else {// 给表格没查到的字段补默认值
          mappedRow.platform_name_id = isFinite(Number(mappedRow.platform_name_id)) ? Number(mappedRow.platform_name_id) : 0;
          mappedRow.org_id = isFinite(Number(mappedRow.org_id)) ? Number(mappedRow.org_id) : 0;
        }
        // 给表格没有的字段补默认值
        mappedRow.order_code_id = isFinite(Number(mappedRow.order_code_id)) ? Number(mappedRow.order_code_id) : 0;
        mappedRow.order_busi_type_dict_id = isFinite(Number(mappedRow.order_busi_type_dict_id)) ? Number(mappedRow.order_busi_type_dict_id) : 0;
        mappedRow.year = isFinite(Number(mappedRow.year)) ? Number(mappedRow.year) : 0;
        mappedRow.month = isFinite(Number(mappedRow.month)) ? Number(mappedRow.month) : 0;
        mappedRow.equ_category_dict_id = isFinite(Number(mappedRow.equ_category_dict_id)) ? Number(mappedRow.equ_category_dict_id) : 0;
        mappedRow.is_deposit_to_rent = isFinite(Number(mappedRow.is_deposit_to_rent)) ? Number(mappedRow.is_deposit_to_rent) : 0;
        mappedRow.is_no_deposit = isFinite(Number(mappedRow.is_no_deposit)) ? Number(mappedRow.is_no_deposit) : 0;
        mappedRow.rent = isFinite(Number(mappedRow.rent)) ? Number(mappedRow.rent) : 0;
        mappedRow.del_flg = isFinite(Number(mappedRow.del_flg)) ? Number(mappedRow.del_flg) : 0;

        // 去重：查找数据库中是否已存在该微信订单号
        const exist = await publicModel.getOne(
          `SELECT wx_trade_num FROM wechat_billing WHERE wx_trade_num='${mappedRow.wx_trade_num}' LIMIT 1`
        );
        if (exist) {
          continue; // 已存在则跳过
        }

        await publicModel.create(
          sqlQueryData.createWechatBillingInsertQuery(mappedRow)
        )
      }

      res.json(success(null, '导入成功'))
    } catch (err) {
      if (res.headersSent) return
      res.status(500).json(serverError(err.message))
    }
  },
]
